# HeidiSQL Dump 
#
# --------------------------------------------------------
# Host:                 localhost
# Database:             tyms
# Server version:       5.0.27-community
# Server OS:            Win32
# max_allowed_packet:   1048576
# HeidiSQL version:     3.0 RC2 Revision: 55
# --------------------------------------------------------

CREATE DATABASE tyms;

USE tyms;


#
# Table structure for table 'customer_company'
#

DROP TABLE IF EXISTS `customer_company`;
CREATE TABLE `customer_company` (
  `CUSTOMER_COMPANY_ID` bigint(20) NOT NULL auto_increment,
  `CUSTOMER_COMPANY_NAME` varchar(20) NOT NULL,
  `ADDRESS` varchar(200) NOT NULL,
  `POSTAL_CODE` varchar(10) NOT NULL,
  `FAX_NUMBER` varchar(20) default NULL,
  `PHONE_NUMBER` varchar(50) default NULL,
  PRIMARY KEY  (`CUSTOMER_COMPANY_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


#
# Dumping data for table 'customer_company'
#

DROP TABLE IF EXISTS `employee`;
CREATE TABLE `employee` (
  `EMPLOYEE_ID` bigint(20) NOT NULL auto_increment,
  `EMPLYEE_NAME` varchar(20) NOT NULL,
  `DEPARTMENT_ID` bigint(20) default NULL,
  `DEGREE_ID` bigint(20) default NULL,
  `REGISTER_TIME` datetime default NULL,
  `PHONE_NUMBER` varchar(50) default NULL,
  `SOCIAL_ID` varchar(20) default NULL,
  `ADDRESS` varchar(200) default NULL,
  `END_TIME_OF_INTERN` datetime default NULL,
  `TIME_OF_EMLOYMENT` datetime default NULL,
  `SOCIAL_STATUS_ID` bigint(20) default NULL,
  `TIME_OF_LEAVING` datetime default NULL,
  `CONTECT_IN_BJ` varchar(20) NOT NULL,
  `PHONE_OF_CONTACT_IN_BJ` varchar(50) NOT NULL,
  `ADDRESS_IN_BJ` varchar(20) NOT NULL,
  `REMARKS` varchar(200) default NULL,
  `POSITION_ID` bigint(20) default NULL,
  `USER_INFO_ID` bigint(20) default NULL,
  PRIMARY KEY  (`EMPLOYEE_ID`),
  UNIQUE KEY `EMPLYEE_NAME` (`EMPLYEE_NAME`),
  KEY `FK75C8D6AE6B9309A6` (`DEPARTMENT_ID`),
  KEY `FK75C8D6AEC4815302` (`POSITION_ID`),
  KEY `FK75C8D6AE29C073EC` (`DEGREE_ID`),
  KEY `FK75C8D6AE10663B8B` (`USER_INFO_ID`),
  KEY `FK75C8D6AE901926B4` (`SOCIAL_STATUS_ID`),
  CONSTRAINT `FK75C8D6AE901926B4` FOREIGN KEY (`SOCIAL_STATUS_ID`) REFERENCES `dictionary_sub` (`DICTIONARY_SUB_ID`),
  CONSTRAINT `FK75C8D6AE10663B8B` FOREIGN KEY (`USER_INFO_ID`) REFERENCES `user` (`USER_ID`),
  CONSTRAINT `FK75C8D6AE29C073EC` FOREIGN KEY (`DEGREE_ID`) REFERENCES `dictionary_sub` (`DICTIONARY_SUB_ID`),
  CONSTRAINT `FK75C8D6AE6B9309A6` FOREIGN KEY (`DEPARTMENT_ID`) REFERENCES `dictionary_sub` (`DICTIONARY_SUB_ID`),
  CONSTRAINT `FK75C8D6AEC4815302` FOREIGN KEY (`POSITION_ID`) REFERENCES `company_position` (`POSITION_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

#
# Table structure for table 'bus_customer'
#

DROP TABLE IF EXISTS `bus_customer`;
CREATE TABLE `bus_customer` (
  `CUSTOMER_ID` bigint(20) NOT NULL auto_increment,
  `CUSTOMER_NAME` varchar(20) NOT NULL,
  `POSITION` varchar(50) default NULL,
  `SEX` varchar(10) default NULL,
  `PHONE_NUMBER` varchar(50) default NULL,
  `CUSTOMER_COMPANY_ID` bigint(20) default NULL,
  PRIMARY KEY  (`CUSTOMER_ID`),
  KEY `FKF7B84ADDA45940FF` (`CUSTOMER_COMPANY_ID`),
  CONSTRAINT `FKF7B84ADDA45940FF` FOREIGN KEY (`CUSTOMER_COMPANY_ID`) REFERENCES `customer_company` (`CUSTOMER_COMPANY_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


#
# Dumping data for table 'bus_customer'
#



#
# Table structure for table 'bus_order'
#

DROP TABLE IF EXISTS `bus_order`;
CREATE TABLE `bus_order` (
  `ORDER_ID` bigint(20) NOT NULL auto_increment,
  `ORDER_STATUS` tinyint(4) NOT NULL default '0',
  `PRO_COST` double default NULL,
  `CUST_REMIT_DATE` datetime default NULL,
  `GETTING_FUND_DATE` datetime default NULL,
  `INVOICE_CONTENTS` varchar(200) default NULL,
  `INVOICE_DATE` datetime default NULL,
  `HOTEL_COMMENTS` varchar(200) default NULL,
  `HOTEL_EXPENSE` double NOT NULL,
  `EDITABLE` bit(1) NOT NULL default '\0',
  `DELETABLE` bit(1) NOT NULL default '\0',
  `EMPLOYEE_ID` bigint(20) NOT NULL,
  `CUSTOMER_COMPANY_ID` bigint(20) NOT NULL,
  PRIMARY KEY  (`ORDER_ID`),
  KEY `FK84A9AC8FA45940FF` (`CUSTOMER_COMPANY_ID`),
  KEY `FK84A9AC8F1DBEA8E2` (`EMPLOYEE_ID`),
  CONSTRAINT `FK84A9AC8F1DBEA8E2` FOREIGN KEY (`EMPLOYEE_ID`) REFERENCES `employee` (`EMPLOYEE_ID`),
  CONSTRAINT `FK84A9AC8FA45940FF` FOREIGN KEY (`CUSTOMER_COMPANY_ID`) REFERENCES `customer_company` (`CUSTOMER_COMPANY_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


#
# Dumping data for table 'bus_order'
#



#
# Table structure for table 'company_position'
#

DROP TABLE IF EXISTS `company_position`;
CREATE TABLE `company_position` (
  `POSITION_ID` bigint(20) NOT NULL auto_increment,
  `POSITION_NAME` varchar(50) NOT NULL,
  `PARENT_ID` bigint(20) default NULL,
  `DEPARTMENT_ID` bigint(20) default NULL,
  PRIMARY KEY  (`POSITION_ID`),
  KEY `FK34EF121E6B9309A6` (`DEPARTMENT_ID`),
  KEY `FK34EF121E481927E1` (`PARENT_ID`),
  CONSTRAINT `FK34EF121E481927E1` FOREIGN KEY (`PARENT_ID`) REFERENCES `company_position` (`POSITION_ID`),
  CONSTRAINT `FK34EF121E6B9309A6` FOREIGN KEY (`DEPARTMENT_ID`) REFERENCES `dictionary_sub` (`DICTIONARY_SUB_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


#
# Dumping data for table 'company_position'
#

/*!40000 ALTER TABLE company_position DISABLE KEYS */;
LOCK TABLES company_position WRITE;
INSERT INTO `company_position` (`POSITION_ID`, `POSITION_NAME`, `PARENT_ID`, `DEPARTMENT_ID`) VALUES ('1','????',NULL,'401'), ('2','??',NULL,'401'), ('3','???',NULL,'401'), ('4','??????',NULL,'401'), ('5','????',NULL,'401'), ('6','????',NULL,'401'), ('7','??',NULL,'402'), ('8','???',NULL,'402'), ('9','????A?',NULL,'402'), ('10','????B?',NULL,'402'), ('11','??',NULL,'403'), ('12','????',NULL,'403'), ('13','????',NULL,'403'), ('14','????',NULL,'404'), ('15','??',NULL,'404'), ('16','????',NULL,'404'), ('17','??',NULL,'404'), ('18','????',NULL,'405'), ('19','??????',NULL,'406'), ('21','????A?',NULL,'407'), ('22','????B?',NULL,'407'), ('23','????',NULL,'410'), ('24','????',NULL,'410'), ('25','???',NULL,'411'), ('26','?????',NULL,'411');
UNLOCK TABLES;
/*!40000 ALTER TABLE company_position ENABLE KEYS */;






#
# Table structure for table 'dictionary_sub'
#

DROP TABLE IF EXISTS `dictionary_sub`;
CREATE TABLE `dictionary_sub` (
  `DICTIONARY_SUB_ID` bigint(20) NOT NULL auto_increment,
  `Dictionary_ID` bigint(20) NOT NULL,
  `DICTIONARY_SUB_NAME` varchar(100) default NULL,
  `CODE` varchar(50) default NULL,
  PRIMARY KEY  (`DICTIONARY_SUB_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


#
# Dumping data for table 'dictionary_sub'
#

/*!40000 ALTER TABLE dictionary_sub DISABLE KEYS */;
LOCK TABLES dictionary_sub WRITE;
INSERT INTO `dictionary_sub` (`DICTIONARY_SUB_ID`, `Dictionary_ID`, `DICTIONARY_SUB_NAME`, `CODE`) VALUES ('101','1','?','MALE'), ('102','1','?','FEMALE'), ('201','2','?','NONE'), ('202','2','???','REGISTERING'), ('301','3','??','OTHER'), ('302','3','??','DOCTOR'), ('303','3','??','MASTER'), ('304','3','??','BACHELOR'), ('401','4','???','MARKETING'), ('402','4','???','ADMINISTRATION'), ('403','4','?????','HUMAN_RESOURCE'), ('404','4','???','FINANCIAL'), ('405','4','???','EDUCATION'), ('406','4','???','INTERNAL_TRAINING'), ('407','4','??????','CONFERENCE_SERVCE'), ('408','4','?????','ENTERPRISE_PLANNING'), ('409','4','?????','NETWORK_TECHNIQUE'), ('410','4','?????','COURSE_DEVELOPING'), ('411','4','??????','PRESIDENT_OFFICE'), ('501','5','NONE','NONE'), ('502','5','REGISTERING','REGISTERING'), ('601','6','PRESIDENT','PRESIDENT'), ('602','6','SUPERVISOR','SUPERVISOR');
UNLOCK TABLES;
/*!40000 ALTER TABLE dictionary_sub ENABLE KEYS */;



#
# Table structure for table 'function'
#

DROP TABLE IF EXISTS `function`;
CREATE TABLE `function` (
  `FUNCTION_ID` bigint(20) NOT NULL auto_increment,
  `FUNCTION_NAME` varchar(50) NOT NULL,
  `PARENT_ID` bigint(20) default NULL,
  `ROOT_URL` varchar(50) default NULL,
  `NAME_SPACE` varchar(100) default NULL,
  `IS_DEFAULT` bit(1) NOT NULL default '\0',
  PRIMARY KEY  (`FUNCTION_ID`),
  KEY `FK80F563D86DD60290` (`PARENT_ID`),
  CONSTRAINT `FK80F563D86DD60290` FOREIGN KEY (`PARENT_ID`) REFERENCES `function` (`FUNCTION_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


#
# Dumping data for table 'function'
#

/*!40000 ALTER TABLE function DISABLE KEYS */;
LOCK TABLES function WRITE;
INSERT INTO `function` (`FUNCTION_ID`, `FUNCTION_NAME`, `PARENT_ID`, `ROOT_URL`, `NAME_SPACE`, `IS_DEFAULT`) VALUES ('1','??????',NULL,'','',''), ('102','????','1','employee/employee.action','/employee',''), ('103','????','1','customerCompany/customerCompany.action','/customerCompany',''), ('104','????','1','role/role.action','/permission','');
UNLOCK TABLES;
/*!40000 ALTER TABLE function ENABLE KEYS */;


#
# Table structure for table 'permission'
#

DROP TABLE IF EXISTS `permission`;
CREATE TABLE `permission` (
  `PERMISSION_ID` bigint(20) NOT NULL auto_increment,
  `ROLE_ID` bigint(20) default NULL,
  `Function_ID` bigint(20) default NULL,
  `PERMISSION` bigint(20) NOT NULL default '1',
  PRIMARY KEY  (`PERMISSION_ID`),
  KEY `FKFE0FB1CF939227E2` (`ROLE_ID`),
  KEY `FKFE0FB1CF7E6AA0A2` (`Function_ID`),
  CONSTRAINT `FKFE0FB1CF7E6AA0A2` FOREIGN KEY (`Function_ID`) REFERENCES `function` (`FUNCTION_ID`),
  CONSTRAINT `FKFE0FB1CF939227E2` FOREIGN KEY (`ROLE_ID`) REFERENCES `role` (`ROLE_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


#
# Dumping data for table 'permission'
#



#
# Table structure for table 'role'
#

DROP TABLE IF EXISTS `role`;
CREATE TABLE `role` (
  `ROLE_ID` bigint(20) NOT NULL auto_increment,
  `ROLE_NAME` varchar(20) NOT NULL,
  `IS_ADMIN` tinyint(1) NOT NULL default '0',
  PRIMARY KEY  (`ROLE_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


#
# Dumping data for table 'role'
#

/*!40000 ALTER TABLE role DISABLE KEYS */;
LOCK TABLES role WRITE;
INSERT INTO `role` (`ROLE_ID`, `ROLE_NAME`, `IS_ADMIN`) VALUES ('1','???',1);
UNLOCK TABLES;
/*!40000 ALTER TABLE role ENABLE KEYS */;


#
# Table structure for table 'user'
#

DROP TABLE IF EXISTS `user`;
CREATE TABLE `user` (
  `USER_ID` bigint(20) NOT NULL auto_increment,
  `LOGIN_ID` varchar(10) default NULL,
  `LOGIN_PWD` varchar(10) default NULL,
  `CREATED_DATE` datetime NOT NULL,
  `LAST_MODIFIED` datetime NOT NULL,
  `ROLE_ID` bigint(20) default NULL,
  PRIMARY KEY  (`USER_ID`),
  KEY `FK27E3CB939227E2` (`ROLE_ID`),
  CONSTRAINT `FK27E3CB939227E2` FOREIGN KEY (`ROLE_ID`) REFERENCES `role` (`ROLE_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


#
# Dumping data for table 'user'
#

/*!40000 ALTER TABLE user DISABLE KEYS */;
LOCK TABLES user WRITE;
INSERT INTO `user` (`USER_ID`, `LOGIN_ID`, `LOGIN_PWD`, `CREATED_DATE`, `LAST_MODIFIED`, `ROLE_ID`) VALUES ('1','admin','111111','1899-12-30 19:00:00','1899-12-30 19:00:00','1');
UNLOCK TABLES;
/*!40000 ALTER TABLE user ENABLE KEYS */;
